﻿USE DangKyMonHocHocPhi;
go
CREATE procedure [dbo].[GetAllSubjectFromDatabase] as
Begin
-- select a.MaMon as MaMon, a.TenMon as TenMon, a.SoTiet as SoTiet, b.TenLoai as TenLoai from MONHOC as a LEFT JOIN LOAIMONHOC as b ON a.MaLoaiMH=b.MaLoaiMH;

SELECT * FROM v_MONHOC;
end
RETURN
GO

CREATE procedure [dbo].[GetInfoPrerequisiteSubjectByIdSubjectFromDatabase] 
(@MaMon nvarchar(20))
as
Begin
select a.MaMHTienQuyet as MaMon, b.TenMon as TenMon, b.SoTiet as SoTiet, b.MaLoaiMH as MaLoaiMH from CT_MONHOC as a inner join MONHOC as b on a.MaMHTienQuyet=b.MaMon where a.MaMon=@MaMon order by a.MaMHTienQuyet ASC;
end
RETURN

GO

CREATE VIEW [dbo].[v_MONHOC]
AS
select MaMon, 
		TenMon, 
		SoTiet, 
		MaLoaiMH, 
		(select 
			STUFF((select 
						(', ')+ (select TenMon from MONHOC where MaMon=b.MaMHTienQuyet) 
					from 
						MONHOC as a 
					INNER join 
						CT_MONHOC as b 
					ON a.MaMon=b.MaMon 
					where a.MaMon=d.MaMon order by b.MaMHTienQuyet asc for XML PATH('')),1,1, '')) as MonHocTienQuyet,
		(select 
			STUFF((select 
						(', ')+ (select TenNganh from NGANH where MaNganh=e.MaNganh) 
					from 
						CT_NGANH_MONHOC as e
					where e.MaMon=d.MaMon order by MaNganh asc for XML PATH('')),1,1, '')) as Nganh
from MONHOC as d

Go

CREATE procedure [dbo].[GetInfoMajorByIdSubjectFromDatabase] 
(@MaMon nvarchar(20))
as
Begin
	select a.MaNganh as MaNganh, b.TenNganh as TenNganh, a.MaTTHocKy as MaTTHocKy, a.MaMon as MaMon
	from 
		CT_NGANH_MONHOC as a
	INNER JOIN
		NGANH as b
	ON a.MaNganh=b.MaNganh 
	WHERE a.MaMon=@MaMon order by a.MaNganh ASC;
end
RETURN

Go

CREATE procedure [dbo].[GetAllOrderSemesterFromDatabase] 
as
Begin
	select * from THUTUHOCKY;
end
RETURN

GO

CREATE procedure [dbo].[GetAllMajorFromDatabase] 
as
Begin
	select * from NGANH;
end
RETURN

GO

CREATE PROCEDURE [dbo].[DeleteSubjectFromDatabase] (@MaMon nvarchar(20))
AS
BEGIN
	Delete CT_MONHOC where MaMon = @MaMon;
	Delete CT_NGANH_MONHOC where MaMon=@MaMon;
	Delete MONHOC WHERE MaMon=@MaMon;
	END
	RETURN

GO

CREATE TRIGGER [dbo].[AutoIncrementSubjectId]
ON [dbo].[MONHOC]
FOR INSERT
AS
BEGIN
DECLARE @MAXValue VARCHAR(15),@NEWValue VARCHAR(15),@NEW_ID VARCHAR(15), @OLD_ID VARCHAR(15);
--Lấy giá trị lớn nhất 
IF ((SELECT COUNT(MaMon) FROM MONHOC)>1)
		BEGIN
			SELECT @MAXValue=MAX(MaMon) FROM MONHOC;
		END
	ELSE
		BEGIN
			SET @MAXValue='MH0000000';
		END
--Lấy giá trị ID được chèn vào từ bên ngoài (bất kì, không quan trọng)
select @OLD_ID = MaMon from INSERTED
--Lấy phần chuỗi số đằng sau TL rồi tăng lên 1 đơn vị
SET @NEWValue= REPLACE(@MaxValue,'MH','')+1
-- Kiểm tra NEWValue nếu < 100
-- Nếu <100, thêm vào số 0 sao cho NEWValue có độ dài = 9
SET @NEW_ID = 'MH'+
    CASE
       WHEN LEN(@NEWValue)<7
          THEN REPLICATE('0',7-LEN(@newValue))
          ELSE ''
       END +
       @NEWValue
--Thay thế giá trị Id từ bên ngoài bằng Id vừa được tạo
UPDATE MONHOC SET MaMon = @NEW_ID WHERE MaMon = @OLD_ID
END

GO

CREATE PROCEDURE [dbo].[InsertSubjectToDatabase] (
	@MaMon nvarchar(20),
	@TenMon nvarchar(255),
	@SoTiet int,
	@MaLoaiMH nvarchar(20)
	)
AS
BEGIN
	insert into MONHOC VALUES (@MaMon, @TenMon, @SoTiet, @MaLoaiMH);
	END
	RETURN

GO

Create PROCEDURE [dbo].[InsertPrerequisiteSubjectDetailToDatabase]
(
	@MaMon nvarchar(20),
	@MaMHTienQuyet nvarchar(20)
)
AS
BEGIN
	Insert into CT_MONHOC values ('CTMH00***', @MaMon, @MaMHTienQuyet);
	END
	RETURN
GO

Create PROCEDURE [dbo].[UpdateSubjectToDatabase]
(
	@MaMon nvarchar(20),
	@TenMon nvarchar(255),
	@SoTiet int,
	@MaLoaiMH nvarchar(20)
)
AS
BEGIN
	UPDATE MONHOC SET TenMon=@TenMon, SoTiet=@SoTiet, MaLoaiMH=@MaLoaiMH where MaMon=@MaMon;
	END
	RETURN
GO

Create PROCEDURE [dbo].[DeletePrerequisiteSubjectDetailFromDatabase]
(
	@MaMon nvarchar(20)
)
AS
BEGIN
	DELETE CT_MONHOC WHERE MaMon=@MaMon;
	END
	RETURN
GO

Create PROCEDURE [dbo].[GetAllPrerequisiteSubjectFromDatabase]
AS
BEGIN
	select MaMon, TenMon from MONHOC;
	END
	RETURN
GO

Create TRIGGER [dbo].[AutoIncrementSubjectDetailId]
ON [dbo].[CT_MONHOC]
FOR INSERT
AS
BEGIN
	DECLARE @MAXValue VARCHAR(15),@NEWValue VARCHAR(15),@NEW_ID VARCHAR(15), @OLD_ID VARCHAR(15);
	--Lấy giá trị lớn nhất 
	IF ((SELECT COUNT(MaCTMH) FROM CT_MONHOC)>1)
		BEGIN
			SELECT @MAXValue=MAX(MaCTMH) FROM CT_MONHOC;
		END
	ELSE
		BEGIN
			SET @MAXValue='CTMH00000';
		END
	--Lấy giá trị ID được chèn vào từ bên ngoài (bất kì, không quan trọng)
	select @OLD_ID = MaCTMH from INSERTED
	--Lấy phần chuỗi số đằng sau TL rồi tăng lên 1 đơn vị
	SET @NEWValue= REPLACE(@MaxValue,'CTMH','')+1
	-- Kiểm tra NEWValue nếu < 100
	-- Nếu <100, thêm vào số 0 sao cho NEWValue có độ dài = 9
	SET @NEW_ID = 'CTMH'+
		CASE
		   WHEN LEN(@NEWValue)<5
			  THEN REPLICATE('0',5-LEN(@newValue))
			  ELSE ''
		   END +
		   @NEWValue
	--Thay thế giá trị Id từ bên ngoài bằng Id vừa được tạo
	UPDATE CT_MONHOC SET MaCTMH = @NEW_ID WHERE MaCTMH = @OLD_ID
END

Go
Create PROCEDURE [dbo].[GetIDSubjectFromDatabase]
AS
BEGIN
	select MAX(MaMon) MaMon from MONHOC;
	END
	RETURN
GO

Create PROCEDURE [dbo].[DeleteMajorOfSubjectDetailFromDatabase]
(
	@MaMon nvarchar(20)
)
AS
BEGIN
	DELETE CT_NGANH_MONHOC WHERE MaMon=@MaMon;
	END
	RETURN
GO

Create PROCEDURE [dbo].[InsertMajorOfSubjectDetailToDatabase]
(
	@MaNganh nvarchar(20),
	@MaTTHocKy nvarchar(20),
	@MaMon nvarchar(20)
)
AS
BEGIN
	Insert into CT_NGANH_MONHOC values (@MaNganh, @MaTTHocKy, @MaMon);
	END
	RETURN
GO
